
package cn.hpclub.server.service;

import cn.hpclub.server.constant.Constant;
import cn.hpclub.server.model.Organization;
import cn.hpclub.server.model.SysCodeValue;
import cn.hpclub.server.util.DateKit;
import cn.hpclub.server.util.InterfaceUtil;
import cn.hpclub.server.util.excel.ExportUtils;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.stream.Collectors;

/**
 * @FileName: SchoolSubjectService.java
 * @Author zhl
 * @Description:
 * @Date 2016年12月14日 下午2:38:57
 * @CopyRight ZTE Corporation
 */
public class SchoolSubjectService {
    private static final Logger    logger  = LoggerFactory.getLogger(SchoolSubjectService.class);
    public static SchoolSubjectService service = new SchoolSubjectService();


    /*public static void main(String[] args) {
        String time = "2017-6";
        String checkTime = time+"-30 23:59:59";
        long t1 = DateKit.toDate(checkTime,"yyyy-MM-dd HH:mm:ss").getTime();
        long t2 = System.currentTimeMillis();
        if(t1>=t2){//说明选择的是当月
            time = DateKit.getLastDay();
        }else{//说明选择的不是当月（至少是上个月）
            time  = time+"-30";
        }
        System.out.println(time);
    }*/

    public Page getReportByPage(int pageNumber,int pageSize,ConcurrentHashMap<String, String> searchInfo,String tableName){
        int thisPage = (pageNumber - 1) * pageSize;
        int orgId= Integer.parseInt(searchInfo.get("org_id"));
        String time = searchInfo.get("start_time");
        if(Constant.Table.SCHOOL_SUBJECT_REPORT_MONTH.equalsIgnoreCase(tableName)){
            String checkTime = time+"-30 23:59:59";
            long t1 = DateKit.toDate(checkTime,"yyyy-MM-dd HH:mm:ss").getTime();
            long t2 = System.currentTimeMillis();
            if(t1>=t2){//说明选择的是当月
                time = DateKit.getLastDay();
            }else{//说明选择的不是当月（至少是上个月）
                time  = time+"-30";
            }
        }


        String sql = " SELECT scr.* FROM "+tableName+" scr LEFT JOIN organization o ON scr.org_id=o.id" +
                " WHERE scr.time = ? ";
        String countSql = " SELECT count(scr.id) FROM "+tableName+" scr LEFT JOIN organization o ON " +
                "scr.org_id=o.id WHERE scr.time = ?";
        if(orgId>0 && orgId != 54470){
            sql +=" AND (o.CityCode="+orgId+" OR o.AreaCode="+orgId+") ";
            countSql +=" AND (o.CityCode="+orgId+" OR o.AreaCode="+orgId+") ";
        }


        if(searchInfo.containsKey("displayName")){
            sql +=" AND scr.DisplayName like "+"'%"+searchInfo.get("displayName")+"%'";
            countSql +=" AND scr.DisplayName like "+"'%"+searchInfo.get("displayName")+"%'";
        }
        if(searchInfo.containsKey("className")){
            sql +=" AND scr.class_name like "+"'%"+searchInfo.get("className")+"%'";
            countSql +=" AND scr.class_name like "+"'%"+searchInfo.get("className")+"%'";
        }

       // sql +=" ORDER BY CONVERT( scr.CityName USING gbk )  DESC";
        sql += " LIMIT " + thisPage + "," + pageSize;

        List<Record> list = Db.find(sql,time);
        long totalRow = Db.queryLong(countSql,time);

        int totalPage = (int)(totalRow / (long)pageSize);
        if(totalRow % (long)pageSize != 0L) {
            ++totalPage;
        }
        Page page =  new Page(list,pageNumber,pageSize,totalPage,(int)totalRow);
        return page;
    }
    public String getReportBySXSSFSql(ConcurrentHashMap<String, String> searchInfo,String tableName){

        int orgId= Integer.parseInt(searchInfo.get("org_id"));
        String time = searchInfo.get("start_time");
        String sql = " SELECT scr.* FROM "+tableName+" scr LEFT JOIN organization o ON scr.org_id=o.id " +
                "WHERE scr.time ='"+time+"' ";

        if(orgId>0 && orgId!=54470){
            sql+="AND (o.CityCode="+orgId+" OR o.AreaCode="+orgId+")";
        }
        if(searchInfo.containsKey("displayName")){
            sql +=" AND scr.DisplayName like "+"'%"+searchInfo.get("displayName")+"%'";
        }
        if(searchInfo.containsKey("className")){
            sql +=" AND scr.class_name like "+"'%"+searchInfo.get("className")+"%'";
        }
        return sql;
    }


    public String getReportBySXSSFSqlForMonth(ConcurrentHashMap<String, String> searchInfo,String tableName){

        int orgId= Integer.parseInt(searchInfo.get("org_id"));
        String time = searchInfo.get("start_time");
        String checkTime = time+"-30 23:59:59";
        long t1 = DateKit.toDate(checkTime,"yyyy-MM-dd HH:mm:ss").getTime();
        long t2 = System.currentTimeMillis();
        if(t1>=t2){//说明选择的是当月
            time = DateKit.getLastDay();
        }else{//说明选择的不是当月（至少是上个月）
            time  = time+"-30";
        }
        String sql = " SELECT scr.* FROM "+tableName+" scr LEFT JOIN organization o ON scr.org_id=o.id " +
                "WHERE scr.time ='"+time+"' ";

        if(orgId>0 && orgId!=54470){
            sql+="AND (o.CityCode="+orgId+" OR o.AreaCode="+orgId+")";
        }
        if(searchInfo.containsKey("displayName")){
            sql +=" AND scr.DisplayName like "+"'%"+searchInfo.get("displayName")+"%'";
        }
        if(searchInfo.containsKey("className")){
            sql +=" AND scr.class_name like "+"'%"+searchInfo.get("className")+"%'";
        }
        return sql;
    }




}
